#!C:\perl\bin
use strict;
use warnings;
use lib "/perl/lib";
use Spreadsheet::ParseExcel;
use DBI;
use utf8;
use Encode;

# connection a sql
my $dbh=DBI->connect("DBI:Pg:dbname='mmdn';host=localhost","postgres","mmdn",{'RaiseError'=>1});

my $fichier=shift;
my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse("$fichier");
if (!defined $workbook){
    die $parser->error(),".\n";
}

my $worksheet;
my @dico=('A','B','C','D','E','F','G','H','I','J');

sub trim($){
	my $string = shift;
	$string =~ s/^\s+//;
	$string =~ s/\s+$//;
	if($string=~/ {2}/){
		$string=~ s/ {2}/ /;
	}
	return $string;
}

foreach($workbook->worksheets()){
	$worksheet=$_;
	my $boite='';
	my %result;
	my %boite2=();
	
	my $name = trim($_->get_name());
	my $col_ref=9;
	if($name eq "F645-312" or $name eq "P8 323,319,306,315,318,316" or $name eq "Stable 578,306,316,315,323,319" or $name eq "F823-354" or $name eq "F820-353"
	or $name eq "F624-359 F757-369" or $name eq "F823-354 M515-350" or $name eq "F915-377 M858-364" or $name eq "M970-361 F851-363" or $name eq "M646-371 M852-365"
	or $name eq "M841-360 M838-362" or $name eq "M560-376 F671-374" or $name eq "M849-366 F725-368" or $name eq "F630-378 M535-373" or $name eq "M576-379 F696-380"
	or $name eq "1042-390 F815-370" or $name eq "M838-362" or $name eq "F551-394  M1033-396"){
		$col_ref=10;
	}
	elsif($name eq "F1040-397  F469-392"){$col_ref=11;}
	
	my $ligne=1;
	my $groupe='Cellule';
	my $type_tissu='Cellules';
	
	my ($row_min,$row_max) = $worksheet->row_range();
	my ($col_min, $col_max) = $worksheet->col_range();
		
	for my $row ($row_min .. $row_max+1){ #parcours lignes
		my $box=$boite;
		for my $col ($col_min .. $col_max){ #parcours colonnes
			my %microcebe;
			my $histo;
			
			my $val=$worksheet->get_cell($row,$col_ref);
			if($val and $val->value() ne '' and $val->is_merged()){
				$boite=trim($val->value());
			}
			if(not $val and $col eq $col_ref){ #remise des compteurs à zéro
				$ligne=0;
				$boite='';
				my @size=keys %boite2;
				if($#size>-1){
					$result{"$box -80"}={%boite2};
				}
				%boite2=();
			}
			if($boite ne ''){
				my $val2=$worksheet->get_cell($row,$col);
				if($val2 and $val2->value()=~/([F|M])(\d{1,4})-(\d{1,3}) *(.*)/ and $col<$col_ref){
					$histo=$3;
					$microcebe{'sexe'}=$1;
					$microcebe{'num'}=$2;
					$microcebe{'histo'}=$3;
					$microcebe{'groupe'}=$groupe;
					if($4){
						my $temp=trim($4);
						$microcebe{'type'}="$type_tissu $temp";
					}
					else{$microcebe{'type'}=$type_tissu;}
					$boite2{"$dico[$col]$ligne"}={%microcebe};
				}
				elsif($val2 and $val2->value()=~/^(\d{1,3}) *(.*)/ and $col<$col_ref){
					$microcebe{'histo'}=$1;
					$microcebe{'groupe'}=$groupe;
					if($2){
						my $temp2=trim($2);
						$microcebe{'type'}="$type_tissu $temp2";
					}
					else{$microcebe{'type'}=$type_tissu;}
					$boite2{"$dico[$col]$ligne"}={%microcebe};
				}
			}
		}
		$ligne++;
	}
	#~ foreach my $k1(sort keys %result){
		#~ print "$k1:\n";
		#~ foreach my $k2(sort keys %{$result{$k1}}){
			#~ print "$k2:";
			#~ foreach my $k3(sort keys %{$result{$k1}{$k2}}){
				#~ if($k3 eq 'num'){
					#~ print "$k3: $result{$k1}{$k2}{$k3}, ";
				#~ }
				#~ elsif($k3 eq 'histo'){
					#~ print "$k3: $result{$k1}{$k2}{$k3}, ";
				#~ }
				#~ elsif($k3 eq 'sexe'){
					#~ print "$k3: $result{$k1}{$k2}{$k3}, ";
				#~ }
				#~ elsif($k3 eq 'type'){
					#~ print "$k3: $result{$k1}{$k2}{$k3}, ";
				#~ }
			#~ }
			#~ print "\n";
		#~ }
	#~ }
	

	# ********************* INSERTION DONNEES DANS LA BASE **************************** #
	
	foreach my $k1(sort keys %result){#chaque boite
		foreach my $k2(sort keys %{$result{$k1}}){ #chaque case remplie de la boite
			my $type;
			my $code;
			my $sexe;
			my $histo;
			my $sexe2;
			my $groupe;
			
			foreach my $k3(sort keys %{$result{$k1}{$k2}}){ #chaque valeur dans la case
				if($k3 eq 'type'){ $type=$result{$k1}{$k2}{$k3}; }
				elsif($k3 eq 'sexe'){ $sexe=$result{$k1}{$k2}{$k3}; }
				elsif($k3 eq 'num'){ $code=$result{$k1}{$k2}{$k3}; }
				elsif($k3 eq 'histo'){ $histo=$result{$k1}{$k2}{$k3}; }
				elsif($k3 eq 'groupe'){ $groupe=$result{$k1}{$k2}{$k3}; }
			}	
			
			# ************************************* Création animal ************************************* #
			my $id_anim;
			
			if($code and $histo){ #si on a le code animal et le code histo
				my $histo2;
				($id_anim,$sexe2,$histo2)=$dbh->selectrow_array("SELECT id_animal,sexe,code_histologique FROM Animal WHERE code_animal='$code'");		
				
				if($histo2 and $histo2 ne $histo){ #si le code histo trouvé ne correspond pas au code que l'on a, on fait une nouvelle recherche
					($id_anim,$sexe2)=$dbh->selectrow_array("SELECT id_animal,sexe FROM Animal WHERE code_animal='$code' and code_histologique=$histo");
					
					if(not $id_anim){
						$dbh->do("INSERT INTO Animal(code_animal,sexe,code_histologique) VALUES ('$code','$sexe',$histo)"); 
						$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$code' and code_histologique=$histo");
					}
				}
				if(not $histo2 and $id_anim){ #si l'animal trouvé n'a pas de code histo
					$dbh->do("UPDATE Animal SET code_histologique=$histo WHERE id_animal=$id_anim"); 
				}
				if(not $sexe2 and $id_anim){ #si l'animal trouvé n'a pas de sexe
					$dbh->do("UPDATE Animal SET sexe='$sexe' WHERE id_animal=$id_anim"); 
				}
			}
			elsif(not $code and $histo){ #si on a que le code histo
				$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_histologique=$histo");
			}
			elsif($code and not $histo){ #si on a que le code animal
				($id_anim,$sexe2)=$dbh->selectrow_array("SELECT id_animal,sexe FROM Animal WHERE code_animal='$code'");
				if(not $sexe2 and $id_anim){#si l'animal existe dans la base mais n'a pas de sexe
					$dbh->do("UPDATE Animal SET sexe='$sexe' WHERE id_animal=$id_anim");
				}
				if(not $id_anim){
					$dbh->do("INSERT INTO Animal(code_animal,sexe) VALUES ('$code','$sexe')"); 
					$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$code'");
				}
			}

			if(not $id_anim){ #si l'animal n'existe pas
				if($sexe and $code){ #si on a le sexe et le code_animal
					$dbh->do("INSERT INTO Animal(code_animal,sexe,code_histologique) VALUES ('$code','$sexe',$histo)");
					$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal=(E'$code') AND code_histologique=$histo");					
				}
				elsif($histo and not $code){ #si on a que le code histo
					$dbh->do("INSERT INTO Animal(code_histologique) VALUES ($histo)");
					$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_histologique=$histo");
				}
			}
			
			# ************************************* Création biobanque ************************************* #
			my $id_banque;
			my $new=encode_utf8($groupe);
			my $new2=encode_utf8($k1);
			my $new3=encode_utf8($type);
			$id_banque=$dbh->selectrow_array("SELECT id_biobanque FROM Biobanque WHERE localisation='$k2' AND nom_boite='$new2' AND type_tissu='$new3' AND id_animal=$id_anim AND groupe_organe='$new'");
		
			if(not $id_banque){ #insertion des données dans la biobanque
				$dbh->do("INSERT INTO Biobanque(type_tissu,groupe_organe,nom_boite,localisation,id_animal) VALUES('$new3','$new','$new2','$k2',$id_anim)");
			}
		}
	}
}
$dbh->disconnect();
